﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Cms.Domain;
using Cms.Domain.RepositoryInterfaces;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Transform;

namespace Cms.Data
{
    public class SiteRepository : BaseRepository, ISiteRepository
    {
        public SiteRepository()
        {
        }

        public SiteRepository(ISession session) : base(session)
        {
        }

        #region ISiteRepository Members

        public IList<Site> GetSites()
        {
            var sites = DetachedCriteria.For<Site>()
                    .SetCacheable(true);
            var aliasDomains = DetachedCriteria.For<Site>()
                    .SetFetchMode("AliasDomains", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .SetCacheable(true);
            var locales = DetachedCriteria.For<Site>()
                    .SetFetchMode("Locales", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .SetCacheable(true);
            var folders = DetachedCriteria.For<Site>()
                    .SetFetchMode("Folders", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .SetCacheable(true);
            var siteMediaFolders = DetachedCriteria.For<Site>()
                    .SetFetchMode("MediaFolders", FetchMode.Eager)
                    .SetCacheable(true);
            var mediaFolders = DetachedCriteria.For<MediaFolder>()
                    .SetCacheable(true);
            var mediaSubFolders = DetachedCriteria.For<MediaFolder>()
                    .SetFetchMode("SubFolders", FetchMode.Eager)
                    .SetCacheable(true);
            var categories = DetachedCriteria.For<Site>()
                    .SetFetchMode("Categories", FetchMode.Eager)
                    .SetCacheable(true);
            var subCategories = DetachedCriteria.For<Site>()
                    .SetFetchMode("Categories.SubCategories", FetchMode.Eager)
                    .SetCacheable(true);
            IList result = Session.CreateMultiCriteria()
                .Add(sites)
                .Add(aliasDomains)
                .Add(locales)
                .Add(folders)
                .Add(siteMediaFolders)
                .Add(mediaFolders)
                .Add(mediaSubFolders)
                .Add(categories)
                .Add(subCategories)
                .SetCacheable(true)
                .List();
            return ((IList)result[0]).Cast<Site>().ToList();
        }

        public Site GetSite(int id)
        {
            return Session.Get<Site>(id);
        }

        public void SaveSite(Site site)
        {
            SaveEntity(site, "SiteRepository.SaveSite");
        }

        public void DeleteSite(int id)
        {
            using (ITransaction tx = Session.BeginTransaction())
            {
                try
                {
                    IDbCommand cmd = Session.Connection.CreateCommand();
                    cmd.Connection = Session.Connection;
                    tx.Enlist(cmd);
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "DELETE FROM cmsVisitedPublicUrl WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupAllowedPage WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupDeniedPage WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsAdministrator SET DefaultSiteID = NULL WHERE DefaultSiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsAdministrator SET CurrentSiteID = NULL WHERE CurrentSiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsApplicationPageArea WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsApplicationPage WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsContentTranslation SET PublishedVersionID = NULL WHERE ContentID IN (" +
                                      "SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupAllowedCustomAdminPage WHERE CustomAdminPageID IN (" +
                                        "SELECT ID FROM cmsCustomAdminPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupDeniedCustomAdminPage WHERE CustomAdminPageID IN (" +
                                        "SELECT ID FROM cmsCustomAdminPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMasterTemplateContentSetTranslationVersion WHERE TranslationVersionID IN (" +
                                        "SELECT ID FROM cmsContentTranslationVersion WHERE TranslationID IN (" +
                                        "SELECT ID FROM cmsContentTranslation WHERE ContentID IN (" +
                                        "SELECT ID FROM cmsContent WHERE SiteID = " + id + ")))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsDetailTemplateContentSetTranslationVersion WHERE TranslationVersionID IN (" +
                                        "SELECT ID FROM cmsContentTranslationVersion WHERE TranslationID IN (" +
                                        "SELECT ID FROM cmsContentTranslation WHERE ContentID IN (" +
                                        "SELECT ID FROM cmsContent WHERE SiteID = " + id + ")))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPageContentSetTranslationVersion WHERE TranslationVersionID IN (" +
                                        "SELECT ID FROM cmsContentTranslationVersion WHERE TranslationID IN (" +
                                        "SELECT ID FROM cmsContentTranslation WHERE ContentID IN (" +
                                        "SELECT ID FROM cmsContent WHERE SiteID = " + id + ")))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMasterTemplateContentSet WHERE PublishedVersionID IN (" +
                                      "SELECT ID FROM cmsContentTranslationVersion WHERE TranslationID IN (" +
                                      "SELECT ID FROM cmsContentTranslation WHERE ContentID IN (" +
                                      "SELECT ID FROM cmsContent WHERE SiteID = " + id + ")))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsDetailTemplateContentSet WHERE PublishedVersionID IN (" +
                                      "SELECT ID FROM cmsContentTranslationVersion WHERE TranslationID IN (" +
                                      "SELECT ID FROM cmsContentTranslation WHERE ContentID IN (" +
                                      "SELECT ID FROM cmsContent WHERE SiteID = " + id + ")))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPageContentSet WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsScheduledPublishing WHERE ContentTranslationVersionID IN (" +
                                      "SELECT ID FROM cmsContentTranslationVersion WHERE TranslationID IN (" +
                                      "SELECT ID FROM cmsContentTranslation WHERE ContentID IN (" +
                                      "SELECT ID FROM cmsContent WHERE SiteID = " + id + ")))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsContentTranslationVersion WHERE TranslationID IN (" +
                                      "SELECT ID FROM cmsContentTranslation WHERE ContentID IN (" +
                                      "SELECT ID FROM cmsContent WHERE SiteID = " + id + "))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsContentTranslation WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMasterTemplateContent WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsDetailTemplateContent WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPageContent WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsContentCategory WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsComment WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupDeniedContent WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupAllowedContent WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsContentDeselectedUserGroup WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsContentRequiredUserGroup WHERE ContentID IN (SELECT ID FROM cmsContent WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsContent WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMasterTemplateStylesheet WHERE StylesheetID IN (" +
                                      "SELECT ID FROM cmsStylesheet WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsStylesheet WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMenuItemTranslation WHERE MenuItemID IN (" +
                                      "SELECT ID FROM cmsMenuItem WHERE MenuVersionID IN (" +
                                      "SELECT ID FROM cmsMenuVersion WHERE MenuID IN (" +
                                      "SELECT ID FROM cmsMenu WHERE SiteID = " + id + ")))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMenuItem WHERE MenuVersionID IN (" +
                                      "SELECT ID FROM cmsMenuVersion WHERE MenuID IN (" +
                                      "SELECT ID FROM cmsMenu WHERE SiteID = " + id + "))";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsMenu SET PublishedVersionID = NULL WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMenuVersion WHERE MenuID IN (" +
                                      "SELECT ID FROM cmsMenu WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMenu WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsRssFeed WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFileCategory WHERE CategoryID IN (SELECT ID FROM cmsCategory WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFolderCategory WHERE CategoryID IN (SELECT ID FROM cmsCategory WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupDeniedMediaFile WHERE FileID IN (SELECT ID FROM cmsMediaFile WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupAllowedMediaFile WHERE FileID IN (SELECT ID FROM cmsMediaFile WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFileDeselectedUserGroup WHERE FileID IN (SELECT ID FROM cmsMediaFile WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFileRequiredUserGroup WHERE FileID IN (SELECT ID FROM cmsMediaFile WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFileLocalizedProperty WHERE MediaFileID IN (SELECT ID FROM cmsMediaFile WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFile WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsMediaFolder SET ParentID = NULL WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupDeniedMediaFolder WHERE FolderID IN (SELECT ID FROM cmsMediaFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupAllowedMediaFolder WHERE FolderID IN (SELECT ID FROM cmsMediaFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFolderDeselectedUserGroup WHERE FolderID IN (SELECT ID FROM cmsMediaFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFolderRequiredUserGroup WHERE FolderID IN (SELECT ID FROM cmsMediaFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMediaFolder WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPageCategory WHERE CategoryID IN (SELECT ID FROM cmsCategory WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsFolderCategory WHERE CategoryID IN (SELECT ID FROM cmsCategory WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsCategory SET ParentID = NULL WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsCategory WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPageLocalizedProperty WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsComment WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupDeniedPage WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupAllowedPage WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPageDeselectedUserGroup WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPageRequiredUserGroup WHERE PageID IN (SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsUserGroup SET LoginPageID = NULL WHERE LoginPageID IN (" +
                                      "SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsUserGroup SET StartPageID = NULL WHERE StartPageID IN (" +
                                      "SELECT ID FROM cmsPage WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPage WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsDetailTemplatePageArea WHERE DetailTemplateID IN (" +
                                      "SELECT ID FROM cmsDetailTemplate WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsDetailTemplateArea WHERE DetailTemplateID IN (" +
                                      "SELECT ID FROM cmsDetailTemplate WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsSite SET DefaultDetailTemplateID = NULL WHERE ID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsDetailTemplate WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsSite SET DefaultMasterTemplateID = NULL WHERE ID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMasterTemplateArea WHERE MasterTemplateID IN (" +
                                      "SELECT ID FROM cmsMasterTemplate WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMasterTemplatePageArea WHERE MasterTemplateID IN (" +
                                      "SELECT ID FROM cmsMasterTemplate WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsMasterTemplate WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsFolder SET ParentID = NULL WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupDeniedFolder WHERE FolderID IN (SELECT ID FROM cmsFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorGroupAllowedFolder WHERE FolderID IN (SELECT ID FROM cmsFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsFolderDeselectedUserGroup WHERE FolderID IN (SELECT ID FROM cmsFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsFolderRequiredUserGroup WHERE FolderID IN (SELECT ID FROM cmsFolder WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsFolder WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsLocaleTranslation WHERE LocaleID IN (" +
                                      "SELECT ID FROM cmsLocale WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsSite SET DefaultLocaleID = NULL WHERE ID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsAdministrator SET CurrentSiteLocaleID = NULL WHERE CurrentSiteLocaleID IN (" +
                                      "SELECT ID FROM cmsLocale WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsLocale WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPluginTemplateLiteralTranslation WHERE LiteralID IN (" +
                                      "SELECT ptl.ID FROM cmsPluginTemplateLiteral ptl " +
                                      "INNER JOIN cmsPluginTemplate pt ON ptl.TemplateID = pt.ID " +
                                      "WHERE pt.SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPluginTemplateLiteral WHERE TemplateID IN (" +
                                      "SELECT ID FROM cmsPluginTemplate WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsPluginTemplate WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsCustomAdminPage WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE cmsSourceCode SET CurrentVersionID = NULL WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsSourceCodeVersion WHERE SourceCodeID IN (" +
                                      "SELECT ID FROM cmsSourceCode WHERE SiteID = " + id + ")";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsSourceCode WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsSiteAliasDomain WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsApplication WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsVisitedAdminUrl WHERE CurrentSiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsAdministratorAvailableSite WHERE SiteID = " + id;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM cmsSite WHERE ID = " + id;
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
                catch (Exception ex)
                {
                    RollbackTransaction("SiteRepository.DeleteSite", tx, ex);
                    throw;
                }
            }
        }
        
        public MasterTemplate GetMasterTemplate(int id)
        {
            return Session.Get<MasterTemplate>(id);
        }
        
        public DetailTemplate GetDetailTemplate(int id)
        {
            return Session.Get<DetailTemplate>(id);
        }

        public Stylesheet GetStylesheet(int id)
        {
            return Session.Get<Stylesheet>(id);
        }
        
        public void DeleteMasterTemplate(int id)
        {
            DeleteHql("from MasterTemplate where Id = " + id, "SiteRepository.DeleteMasterTemplate");
        }
        
        public void DeleteDetailTemplate(int id)
        {
            DeleteHql("from DetailTemplate where Id = " + id, "SiteRepository.DeleteDetailTemplate");
        }
        
        public IList<SourceCodeVersion> GetSourceCodeWithReferencesTo(string virtualPath, int siteId)
        {
            string hql = "from SourceCodeVersion where Parent.Site.ID = :siteID and Code like :code";
            IList<SourceCodeVersion> versions = Session.CreateQuery(hql)
                .SetInt32("siteID", siteId)
                .SetString("code", "%" + virtualPath.Replace("'", "''").Replace("[", "_").Replace("]", "_") + "%")
                .List<SourceCodeVersion>();
            return versions;
        }
        
        public Application GetApplication(int id)
        {
            return Session.Get<Application>(id);
        }

        public void DeleteApplication(Application app)
        {
            DeleteEntity(app, "SiteRepository.DeleteApplication");
        }
        
        public void SaveApplication(Application app)
        {
            SaveEntity(app, "SiteRepository.SaveApplication");
        }
        
        public Menu GetMenu(int id, int siteId)
        {
            IMultiQuery multi = Session.CreateMultiQuery();
            multi.Add(Session.CreateQuery("from Menu m where m.ID = " + id).SetCacheable(true));
            multi.Add(Session.CreateQuery("from MenuVersion mv where mv.Parent.ID = " + id).SetCacheable(true));
            multi.Add(Session.CreateQuery("from MenuVersion mv join fetch mv.Items where mv.Parent.ID = " + id).SetCacheable(true));
            multi.Add(Session.CreateQuery("from MenuItem mi join fetch mi.Translations where mi.Version.Parent.ID = " + id).SetCacheable(true));
            multi.Add(Session.CreateQuery("from MenuItem mi join fetch mi.Parent where mi.Version.Parent.ID = " + id).SetCacheable(true));
            multi.Add(Session.CreateQuery("from MenuItem mi join fetch mi.SubItems where mi.Version.Parent.ID = " + id).SetCacheable(true));
            multi.Add(Session.CreateQuery("from MenuItem mi join fetch mi.Page where mi.Version.Parent.ID = " + id).SetCacheable(true));
            multi.Add(Session.CreateQuery("from MenuItem mi join fetch mi.ActiveForPagesInCategory where mi.Version.Parent.ID = " + id).SetCacheable(true));
            IList result = multi.SetCacheable(true).List();
            var menus = result[0] as ArrayList;
            if (menus != null && menus.Count > 0)
            {
                return (Menu)menus[0];
            }
            return null;
        }

        public void DeleteMenu(Menu menu)
        {
            DeleteEntity(menu, "SiteRepository.DeleteMenu");
        }
        
        public void SaveMenu(Menu menu)
        {
            SaveEntity(menu, "SiteRepository.SaveMenu");
        }

        public IList<Folder> GetRootFoldersForExplorer(Site site)
        {
            var pageFolders = DetachedCriteria.For<Page>()
                    .SetFetchMode("Folder", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var pageRequiredUserGroups = DetachedCriteria.For<Page>()
                    .SetFetchMode("RequiredUserGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var pageDeselectedUserGroups = DetachedCriteria.For<Page>()
                    .SetFetchMode("DeselectedUserGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var pageAllowedAdministratorGroups = DetachedCriteria.For<Page>()
                    .SetFetchMode("AllowedAdministratorGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var pageDeniedAdministratorGroups = DetachedCriteria.For<Page>()
                    .SetFetchMode("DeniedAdministratorGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var pageCategories = DetachedCriteria.For<Page>()
                    .SetFetchMode("Categories", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var folderSubFolders = DetachedCriteria.For<Folder>()
                    .SetFetchMode("SubFolders", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var folderPages = DetachedCriteria.For<Folder>()
                    .SetFetchMode("Pages", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var folderRequiredUserGroups = DetachedCriteria.For<Folder>()
                    .SetFetchMode("RequiredUserGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var folderDeselectedUserGroups = DetachedCriteria.For<Folder>()
                    .SetFetchMode("DeselectedUserGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var folderAllowedAdministratorGroups = DetachedCriteria.For<Folder>()
                    .SetFetchMode("AllowedAdministratorGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var folderDeniedAdministratorGroups = DetachedCriteria.For<Folder>()
                    .SetFetchMode("DeniedAdministratorGroups", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);
            var folderCategories = DetachedCriteria.For<Folder>()
                    .SetFetchMode("Categories", FetchMode.Eager)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .Add(Restrictions.Eq("Site.ID", site.ID))
                    .SetCacheable(true);

            var result = Session.CreateMultiCriteria()
                .Add(pageFolders)
                .Add(pageRequiredUserGroups)
                .Add(pageDeselectedUserGroups)
                .Add(pageAllowedAdministratorGroups)
                .Add(pageDeniedAdministratorGroups)
                .Add(pageCategories)
                .Add(folderSubFolders)
                .Add(folderPages)
                .Add(folderRequiredUserGroups)
                .Add(folderDeselectedUserGroups)
                .Add(folderAllowedAdministratorGroups)
                .Add(folderDeniedAdministratorGroups)
                .Add(folderCategories)
                .SetCacheable(true)
                .List();
            //IList list = (IList)result[0];
            //return list[0] as IList<Folder>;
            return site.GetRootFolders();
        }

        public IList<Folder> GetRootFolders(Site site)
        {
            return site.GetRootFolders();
        }

        public IList<MediaFolder> GetRootMediaFolders(Site site)
        {
            Session.CreateCriteria(typeof(MediaFolder))
                .SetFetchMode("SubFolders", FetchMode.Eager)
                .SetFetchMode("RequiredUserGroups", FetchMode.Join)
                .SetFetchMode("DeselectedUserGroups", FetchMode.Join)
                .Add(Restrictions.Eq("Site.ID", site.ID))
                .SetCacheable(true)
                .List<MediaFolder>();
            return site.GetRootMediaFolders();
        }

        #endregion
    }
}